﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using Navigant.Infrastructure.Cases;
using Navigant.Infrastructure.DomainBase;
using Navigant.Infrastructure.EntityFactoryFramework;
using Navigant.Infrastructure.RepositoryFramework;
using Navigant.Model.PreservationNotices;
using Navigant.Infrastructure.Helpers;

namespace Navigant.Infrastructure.Repositories
{
    public class SqlPreservationNoticeRepository : RepositoryBase<PreservationNotice>, IPreservationNoticeRepository
    {
        #region Constants

        private static class Constants
        {
            public const int SmtpVerifierDefaultThresholdSeconds = 30;
            public const string ThresholdSecondsAppSettingName = "thresholdSeconds";
        }

        #endregion

        #region Private Fields

        private ICase currentCase;
        private string connectionString;
        private IEntityFactory<PreservationNotice> entityFactory;

        #endregion

        #region Public Constructors

        public SqlPreservationNoticeRepository(ICase currentCase)
            : this(currentCase, null)
        {
        }

        public SqlPreservationNoticeRepository(ICase currentCase, IUnitOfWork unitOfWork)
            : base(unitOfWork)
        {
            this.currentCase = currentCase;
            this.connectionString = ConfigurationManager.ConnectionStrings["NavigantPortal"].ConnectionString;
            this.entityFactory = EntityFactoryBuilder.BuildFactory<PreservationNotice>();
        }

        #endregion

        #region IPreservationNoticeRepository Members

        #region FindAll

        public override IList<PreservationNotice> FindAll()
        {
            throw new NotImplementedException();
        }

        #endregion

        #region FindBy

        public override PreservationNotice FindBy(object key)
        {
            PreservationNotice notice = null;
            using (SqlConnection connection = new SqlConnection(this.connectionString))
            {
                using (SqlCommand command = new SqlCommand("PreservationNoticeHistoryGetByID",
                           connection))
                {
                    command.CommandTimeout = 0;
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@PreservationNoticeHistoryID", key);
                    connection.Open();
                    using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        if (reader.Read())
                        {
                            notice = this.entityFactory.BuildEntity(reader, this.currentCase);
                        }
                    }
                }
            }
            return notice;
        }

        public IList<PreservationNotice> FindBy(int preservationNoticeTemplateId)
        {
            List<PreservationNotice> notices = new List<PreservationNotice>();
            using (SqlConnection connection = new SqlConnection(this.connectionString))
            {
                using (SqlCommand command = new SqlCommand("PreservationNoticeHistoryGetByNoticeID",
                           connection))
                {
                    command.CommandTimeout = 0;
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@PreservationNoticeID", preservationNoticeTemplateId);
                    connection.Open();
                    using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (reader.Read())
                        {
                            notices.Add(this.entityFactory.BuildEntity(reader, this.currentCase));
                        }
                    }
                }
            }
            return notices;
        }

        #endregion

        #region FindAllUnsent

        public IList<PreservationNotice> FindAllUnsent(int maxResults)
        {
            List<PreservationNotice> notices = new List<PreservationNotice>();
            using (SqlConnection connection = new SqlConnection(this.connectionString))
            {
                using (SqlCommand command = new SqlCommand("PreservationNoticeHistoryGetUnconfirmedByNewSite",
                           connection))
                {
                    command.CommandTimeout = 0;
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@RowCount", maxResults);
                    command.Parameters.AddWithValue("@NewSiteID", this.currentCase.Id);
                    connection.Open();
                    using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (reader.Read())
                        {
                            notices.Add(this.entityFactory.BuildEntity(reader, this.currentCase));
                        }
                    }
                }
            }
            return notices;
        }

        #endregion

        #region FindBySite

        public IList<PreservationNotice> FindBySite()
        {
            List<PreservationNotice> notices = new List<PreservationNotice>();
            {
                using (SqlConnection connection = new SqlConnection(this.connectionString))
                {
                    using (SqlCommand command = new SqlCommand("PreservationNoticeHistoryGetByNewSite",
                               connection))
                    {
                        command.CommandTimeout = 0;
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@NewSiteID", this.currentCase.Id);
                        connection.Open();
                        using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (reader.Read())
                            {
                                notices.Add(this.entityFactory.BuildEntity(reader, this.currentCase));
                            }
                        }
                    }
                }
            }
            return notices;
        }

        public DataTable FindBySiteRaw()
        {
            DataTable notices = new DataTable();
            notices.TableName = "PreservationNotices";
            using (SqlConnection connection = new SqlConnection(this.connectionString))
            {
                using (SqlCommand command = new SqlCommand("PreservationNoticeHistoryGetByNewSite",
                           connection))
                {
                    command.CommandTimeout = 0;
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@NewSiteID", this.currentCase.Id);
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    adapter.Fill(notices);
                }
            }
            return notices;
        }

        public IList<PreservationNotice> FindBySite(int pageSize, int currentPage, out int itemCount)
        {
            itemCount = 0;
            List<PreservationNotice> notices = new List<PreservationNotice>();
            using (SqlConnection connection = new SqlConnection(this.connectionString))
            {
                using (SqlCommand command = new SqlCommand("PreservationNoticeHistoryGetByNewSitePaged",
                           connection))
                {
                    command.CommandTimeout = 0;
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@NewSiteID", this.currentCase.Id);
                    command.Parameters.AddWithValue("@PageSize", pageSize);
                    command.Parameters.AddWithValue("@CurrentPage", currentPage);
                    command.Parameters.AddWithValue("@ItemCount", SqlDbType.Int).Direction = ParameterDirection.Output;
                    connection.Open();
                    using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (reader.Read())
                        {
                            notices.Add(this.entityFactory.BuildEntity(reader, this.currentCase));
                        }
                    }
                    itemCount = Convert.ToInt32(command.Parameters["@ItemCount"].Value);
                }
            }
            return notices;
        }

        #endregion

        #region FindBySiteAnd

        public IList<PreservationNotice> FindBySiteAnd(string messageIdentifier)
        {
            List<PreservationNotice> notices = new List<PreservationNotice>();
            if (!string.IsNullOrEmpty(messageIdentifier))
            {
                using (SqlConnection connection = new SqlConnection(this.connectionString))
                {
                    using (SqlCommand command = new SqlCommand("PreservationNoticeHistoryGetByIdentifier",
                               connection))
                    {
                        command.CommandTimeout = 0;
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@Identifier", messageIdentifier);
                        connection.Open();
                        using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (reader.Read())
                            {
                                notices.Add(this.entityFactory.BuildEntity(reader, this.currentCase));
                            }
                        }
                    }
                }
            }
            return notices;
        }

        public IList<PreservationNotice> FindBySiteAnd(int custodianId)
        {
            List<PreservationNotice> notices = new List<PreservationNotice>();
            using (SqlConnection connection = new SqlConnection(this.connectionString))
            {
                using (SqlCommand command = new SqlCommand("PreservationNoticeHistoryGetByNewSiteCustodian",
                           connection))
                {
                    command.CommandTimeout = 0;
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@NewSiteID", this.currentCase.Id);
                    command.Parameters.AddWithValue("@CustodianID", custodianId);
                    connection.Open();
                    using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (reader.Read())
                        {
                            notices.Add(this.entityFactory.BuildEntity(reader, this.currentCase));
                        }
                    }
                }
            }
            return notices;
        }

        public IList<PreservationNotice> FindBySiteAnd(int preservationNoticeTemplateId,
            int custodianId)
        {
            List<PreservationNotice> notices = new List<PreservationNotice>();
            using (SqlConnection connection = new SqlConnection(this.connectionString))
            {
                using (SqlCommand command = new SqlCommand("PreservationNoticeHistoryGetByNewSiteNoticeCustodian",
                           connection))
                {
                    command.CommandTimeout = 0;
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@NewSiteID", this.currentCase.Id);
                    command.Parameters.AddWithValue("@PreservationNoticeID", preservationNoticeTemplateId);
                    command.Parameters.AddWithValue("@CustodianID", custodianId);
                    connection.Open();
                    using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (reader.Read())
                        {
                            notices.Add(this.entityFactory.BuildEntity(reader, this.currentCase));
                        }
                    }
                }
            }
            return notices;
        }

        #endregion

        #region FindByStatus

        public IList<PreservationNotice> FindByStatus(PreservationNoticeStatus status, int maxResults)
		{
            List<PreservationNotice> notices = new List<PreservationNotice>();
			using (SqlConnection connection = new SqlConnection(this.connectionString))
			{
				using (SqlCommand command = new SqlCommand("PreservationNoticeHistoryGetUnconfirmed", 
						   connection))
				{
                    command.CommandTimeout = 0;
					command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@Status", status.ToString());
					command.Parameters.AddWithValue("@RowCount", maxResults);
					connection.Open();
					using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
					{
						while (reader.Read())
						{
                            notices.Add(this.entityFactory.BuildEntity(reader, this.currentCase));
						}						
					}
				}
			}

            return notices;
        }

        #endregion

        #region FindAttachments

        public IList<Attachment> FindAttachments(int preservationNoticeId)
        {
            List<Attachment> attachments = new List<Attachment>();
            using (SqlConnection connection = new SqlConnection(this.connectionString))
            {
                using (SqlCommand command = new SqlCommand("PreservationNoticeHistoryAttachmentGetByPreservationNoticeHistoryID",
                           connection))
                {
                    command.CommandTimeout = 0;
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@PreservationNoticeHistoryID", preservationNoticeId);
                    connection.Open();
                    using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (reader.Read())
                        {
                            attachments.Add(PreservationNoticeFactory.BuildAttachment(reader));
                        }
                    }
                }
            }
            return attachments;
        }

        #endregion

        #region AssociateToLogEntry

        public void AssociateToLogEntry(PreservationNotice notice, SmtpLogEntry logEntry)
        {
            if (notice != null && logEntry != null)
            {
                using (SqlConnection connection = new SqlConnection(this.connectionString))
                {
                    using (SqlCommand command = new SqlCommand("InetLogUpdatePreservationNoticeHistoryID",
                               connection))
                    {
                        command.CommandTimeout = 0;
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@InetLogID", logEntry.Id);
                        command.Parameters.AddWithValue("@PreservationNoticeHistoryID", notice.Id);
                        connection.Open();
                        command.ExecuteNonQuery();
                    }
                }
            }
        }

        #endregion

        #region FindSmtpLogEntry

        public SmtpLogEntry FindSmtpLogEntry(PreservationNotice notice)
        {
            return this.FindSmtpLogEntry(notice, false);
        }

        public SmtpLogEntry FindSmtpLogEntry(PreservationNotice notice, bool findAllSmtpCodes)
        {
            SmtpLogEntry logEntry = null;

            if (notice != null)
            {
                if (notice.To == null)
                {
                    Logger.LogMessage(string.Format("No custodian record found for Notice History ID {0} and Case Site ID {1} ({2}).",
                    notice.Id, this.currentCase.Id.ToString(), this.currentCase.SiteAddress.ToString()));
                    return null;
                }
                if (!string.IsNullOrEmpty(notice.To.Email))
                {
                    int thresholdSeconds = Constants.SmtpVerifierDefaultThresholdSeconds;
                    if (!string.IsNullOrEmpty(ConfigurationManager.AppSettings[Constants.ThresholdSecondsAppSettingName]))
                    {
                        thresholdSeconds = Convert.ToInt32(ConfigurationManager.AppSettings[Constants.ThresholdSecondsAppSettingName]);
                    }
                    using (SqlConnection connection = new SqlConnection(this.connectionString))
                    {
                        string storedProcedureName = "SmtpLogEntryGet";
                        if (findAllSmtpCodes)
                        {
                            storedProcedureName = "SmtpLogEntryGetWithAllCodes";
                        }
                        using (SqlCommand command = new SqlCommand(storedProcedureName,
                                   connection))
                        {
                            command.CommandType = CommandType.StoredProcedure;
                            command.CommandTimeout = 0;
                            command.Parameters.AddWithValue("@mailTo", notice.To.Email);
                            command.Parameters.AddWithValue("@dateTime", notice.DateMessageCreated);
                            command.Parameters.AddWithValue("@thresholdSeconds", thresholdSeconds);
                            connection.Open();
                            using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                            {
                                if (reader.Read())
                                {
                                    logEntry = PreservationNoticeFactory.BuildLogEntry(reader);
                                }
                            }
                        }
                    }
                }
            }
            return logEntry;
        }

        #endregion

        #region UpdateVerificationTries

        public void UpdateVerificationTries(PreservationNotice notice)
        {
            using (SqlConnection connection = new SqlConnection(this.connectionString))
            {
                using (SqlCommand command = new SqlCommand("PreservationNoticeHistoryUpdateTries",
                                       connection))
                {
                    command.CommandTimeout = 0;
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@PreservationNoticeHistoryID", notice.Id);
                    connection.Open();
                    command.ExecuteNonQuery();
                }
            }
        }

        #endregion

        #endregion

        #region Protected Methods

        protected override int PersistNewItem(PreservationNotice item)
        {
            if (item != null)
            {
                using (SqlConnection connection = new SqlConnection(this.connectionString))
                {
                    using (SqlCommand command = new SqlCommand("PreservationNoticeHistoryInsertNew",
                               connection))
                    {
                        command.CommandTimeout = 0;
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@NewSiteID", item.ParentCase.Id);
                        command.Parameters.AddWithValue("@PreservationNoticeID",
                            item.TemplateId);
                        command.Parameters.AddWithValue("@EventType", item.Status.ToString());
                        command.Parameters.AddWithValue("@PreservationNoticeName",
                            item.TemplateName);
                        command.Parameters.AddWithValue("@DateEmailMessageCreated", (item.DateMessageCreated != DateTime.MinValue ? (object)item.DateMessageCreated : DBNull.Value));
                        command.Parameters.AddWithValue("@DateEmailMessageSent",
                            (item.DateMessageSent != null ? (object)item.DateMessageSent : DBNull.Value));
                        command.Parameters.AddWithValue("@DateAcknowledged",
                            (item.DateMessageAcknowledged != null ? (object)item.DateMessageAcknowledged : DBNull.Value));
                        command.Parameters.AddWithValue("@FromAddress", item.FromAddress);
                        command.Parameters.AddWithValue("@CustodianEmail", item.To.Email);
                        command.Parameters.AddWithValue("@MessageSubject",
                            item.Subject);
                        command.Parameters.AddWithValue("@MessageBody", item.Body);
                        command.Parameters.AddWithValue("@Identifier", item.MessageIdentifier);
                        command.Parameters.AddWithValue("@InterviewDate", DBNull.Value);
                        command.Parameters.AddWithValue("@CustodianID", item.To.Id);
                        command.Parameters.AddWithValue("@CustodianLastName", item.To.Name.LastName);
                        command.Parameters.AddWithValue("@CustodianFirstName", item.To.Name.FirstName);
                        command.Parameters.AddWithValue("@HasAttachments", 0);
                        SqlParameter outputParameter = new SqlParameter();
                        outputParameter.ParameterName = "@PreservationNoticeHistoryID";
                        outputParameter.DbType = DbType.Int32;
                        outputParameter.Size = 4;
                        outputParameter.Direction = ParameterDirection.Output;
                        command.Parameters.Add(outputParameter);
                        connection.Open();
                        command.ExecuteNonQuery();
                        int id = (int)command.Parameters["@PreservationNoticeHistoryID"].Value;
                        foreach (Attachment attachment in item.Attachments)
                        {
                            // TODO:  what about the file name also?  e.g. sample_notice.doc
                            this.AddHistoryAttachment(id, attachment.Content);
                        }
                        return id;
                    }
                }
            }
            else
            {
                return 0;
            }
        }

        protected override void PersistUpdatedItem(PreservationNotice item)
        {
            using (SqlConnection connection = new SqlConnection(this.connectionString))
            {
                using (SqlCommand command = new SqlCommand("PreservationNoticeHistoryUpdateSent",
                                   connection))
                {
                    command.CommandTimeout = 0;
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@PreservationNoticeHistoryID", item.Id);
                    command.Parameters.AddWithValue("@EventType", item.Status.ToString());
                    command.Parameters.AddWithValue("@DateMessageSent", item.DateMessageSent.Value);

                    connection.Open();
                    command.ExecuteNonQuery();
                }
            }
        }

        protected override void PersistDeletedItem(PreservationNotice item)
        {
            throw new NotImplementedException();
        }

        #endregion

        #region Private Methods

        private void AddHistoryAttachment(int preservationNoticeHistoryId, byte[] attachment)
        {
            if (attachment != null)
            {
                using (SqlConnection connection = new SqlConnection(this.connectionString))
                {
                    using (SqlCommand command = new SqlCommand("PreservationNoticeHistoryAttachmentInsert",
                               connection))
                    {
                        command.CommandTimeout = 0;
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@PreservationNoticeHistoryID", preservationNoticeHistoryId);
                        command.Parameters.AddWithValue("@Attachment", attachment);

                        connection.Open();
                        command.ExecuteNonQuery();
                    }
                }
            }
        }

        #endregion
    }
}
